package com.wanxi.dao.Impl;

import com.wanxi.dao.SuggestDao;
import com.wanxi.model.LawyerModel;
import com.wanxi.model.SuggestModel;
import com.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Li
 * @Date 2023/5/6 15:08
 */
public class SuggestDaoImpl implements SuggestDao {
    /**
     * 新增
     *
     * @param suggestModel
     * @return
     */
    public int add(SuggestModel suggestModel) {
        String sql = "INSERT INTO `suggest` (`name`, `phone`, `email`, " +
                "`message`, `create_time`, `update_time`," +
                " `enable`, `remark`) VALUES ('" + suggestModel.getName() + "', '" + suggestModel.getPhone() + "', " +
                "'" + suggestModel.getEmail() + "', '" + suggestModel.getMessage() + "', now(), " +
                "sysdate(), '" + suggestModel.getEnable() + "', '" + suggestModel.getRemark() + "');";
        return JDBC.update(sql);
    }

    /**
     * 修改
     *
     * @param suggestModel
     * @return
     */
    public int update(SuggestModel suggestModel) {
        String sql = "UPDATE `suggest` SET `name` = '" + suggestModel.getName() + "', `phone` = '" + suggestModel.getPhone() + "'," +
                " `email` = '" + suggestModel.getEmail() + "', `message` = '" + suggestModel.getMessage() + "', `update_time` = now()," +
                " `enable` = '" + suggestModel.getEnable() + "', `remark` = '" + suggestModel.getRemark() + "' WHERE (`id` = '" + suggestModel.getId() + "');";
        return JDBC.update(sql);
    }

    /**
     * 删除
     *
     * @param suggestModel
     * @return
     */
    public int delete(SuggestModel suggestModel) {
        String sql = "delete from `suggest` where id='" + suggestModel.getId() + "' ";
        return JDBC.update(sql);
    }

    public List<SuggestModel> findAll(SuggestModel suggestModel) {
        int start = (suggestModel.getPage()-1)*suggestModel.getLimit();
        String sql = "select * from `suggest` where true";
        sql += getWhereSql(suggestModel);
        sql += " order by id desc";
        sql += " limit "+start+","+suggestModel.getLimit()+" ";
        ResultSet query = JDBC.query(sql);
        List<SuggestModel> list = new ArrayList<SuggestModel>();
        try {
            while (query.next()) {
                list.add(SuggestModel.builder()
                        .id(query.getInt("id"))
                        .name(query.getString("name"))
                        .phone(query.getString("phone"))
                        .email(query.getString("email"))
                        .message(query.getString("message"))
                        .enable(query.getInt("enable"))
                        .remark(query.getString("remark"))
                        .build());
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }


    private String getWhereSql(SuggestModel suggestModel){
        String sql = "";
        if (!"".equals(suggestModel.getName())) {
            sql += " and name like '%" + suggestModel.getName() + "%'";
        }
        return sql;
    }
    /**
     * 根据id查询
     * @param suggestModel
     * @return
     */
    public SuggestModel findById(SuggestModel suggestModel) {
        String sql = "select * from `suggest` where id='"+suggestModel.getId()+"' ";
        ResultSet query = JDBC.query(sql);
        try{
            while (query.next()){
                return SuggestModel.builder()
                        .id(query.getInt("id"))
                        .name(query.getString("name"))
                        .phone(query.getString("phone"))
                        .email(query.getString("email"))
                        .message(query.getString("message"))
                        .enable(query.getInt("enable"))
                        .remark(query.getString("remark"))
                        .build();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public Integer count(SuggestModel suggestModel) {
        String sql = "select count(*) as count from `suggest` where true";
      sql += getWhereSql(suggestModel);
//        ResultSet query = JDBC.query(sql);
//        try{
//            while (query.next()){
//                return query.getInt("count");
//            }
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
        return JDBC.count(sql);
    }

    @Override
    public Integer totalCount() {
        String sql = "select count(*) as count from `suggest`";
        return JDBC.count(sql);
    }
}
